package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import utils.Constantes;
import utils.Utils;

import model.Usuario;
import model.Habilitacao;
import model.Servico;


public class HabilitacaoDao extends GenericDao{
	
	private int numerodecampos = 2;
	
	public HabilitacaoDao(){
		super();
	}
	public PreparedStatement adicionar(Habilitacao habilitacao) throws SQLException{
		
		String sql = "insert into habilitacao(idusuario,idservico) values (?,?)";
		

		this.setQuery(sql);
		ArrayList<Integer> indexes=new ArrayList<Integer>();
		for (int i=0;i<numerodecampos;i++)
			indexes.add(i,i+1);
		indexes.add(numerodecampos,null);
		
		PreparedStatement stmt = this.loadModelToPreparedStatement(habilitacao,indexes);
		
		stmt.execute();	
		this.setQuery(stmt.toString());

		stmt.close();
		
		con.close();
		return stmt;
		
	}

	
	
	public PreparedStatement desativar(Habilitacao habilitacao) throws SQLException{
		String sql = "update habilitacao set ativo = 'N' where idhabilitacao = ?";
		
		

		this.setQuery(sql);
		ArrayList<Integer> indexes=new ArrayList<Integer>();
		for (int i=0;i<numerodecampos;i++)
			indexes.add(i,null);
		indexes.add(numerodecampos,1);
		PreparedStatement stmt = this.loadModelToPreparedStatement(habilitacao, indexes);
		
		
		
		stmt.execute();
		this.setQuery(stmt.toString());

		stmt.close();
		
		con.close();
		return stmt;
		

	}

	

	public PreparedStatement alterar(Habilitacao habilitacao) throws SQLException{
		
		String sql = "update habilitacao set idusuario=?,idservico=?" +
				" where idhabilitacao = ? and ativo = 'S'";
		
		this.setQuery(sql);
		ArrayList<Integer> indexes=new ArrayList<Integer>();
		for (int i=0;i<=numerodecampos;i++)
			indexes.add(i,i+1);
		
		
		PreparedStatement stmt =this.loadModelToPreparedStatement(habilitacao,indexes); 
			
		
		stmt.execute();
		
		this.setQuery(stmt.toString());

		stmt.close();
		
		con.close();
		return stmt;
		
	}
	

	
	public ArrayList<Habilitacao> getHabilitacaos(Habilitacao habilitacaoBusca, int limite, int inicio) throws SQLException{
		String sql = "select * from habilitacao where 1=1 and ativo = 'S'";
		this.setQuery(sql);
		ArrayList<Integer> restrictions=this.prepareRestrictionsQuery(habilitacaoBusca);
		this.addLimit(limite,inicio);
		PreparedStatement stmt = this.addRestrictions(habilitacaoBusca, restrictions);
		

		
		ResultSet rs = stmt.executeQuery();
		Habilitacao habilitacao = null;
		ArrayList<Habilitacao> habilitacaos=new ArrayList<Habilitacao>();
		
		while(rs.next()){
			habilitacao = this.loadResultSetToModel(rs);
			habilitacaos.add(habilitacao);
			
		} 
		this.setQuery(stmt.toString());

		stmt.close();
		rs.close();
		con.close();
		
		return habilitacaos;
		
	}
	
	public Integer countGetHabilitacaos(Habilitacao habilitacaoBusca) throws SQLException{
		String sql = "select count(*) as count from habilitacao where 1=1 and ativo = 'S'";
		this.setQuery(sql);
		ArrayList<Integer> restrictions=this.prepareRestrictionsQuery(habilitacaoBusca);
		PreparedStatement stmt = this.addRestrictions(habilitacaoBusca, restrictions);
		
		ResultSet rs = stmt.executeQuery();
		Integer count = new Integer(0);
		
		while(rs.next()){
			if (!Utils.isBlank(rs.getInt(Constantes.COUNT)))
				count=rs.getInt(Constantes.COUNT);
		} 
		this.setQuery(stmt.toString());

		stmt.close();
		rs.close();
		con.close();
		
		return count;
		
	}
	
	
	private PreparedStatement addRestrictions(Habilitacao habilitacao,
			ArrayList<Integer> indexes) throws SQLException {
PreparedStatement stmt=con.prepareStatement(this.getQuery());
		
		if (!Utils.isBlank(indexes.get(0)))
			stmt.setInt(indexes.get(0), habilitacao.getUsuario().getId());
		
		if (!Utils.isBlank(indexes.get(1)))
			stmt.setInt(indexes.get(1), habilitacao.getServico().getId());
		
		if (!Utils.isBlank(indexes.get(2)))
			stmt.setInt(indexes.get(2), habilitacao.getId());
		
		
		return stmt;
	}
	
	
	
	private ArrayList<Integer> prepareRestrictionsQuery(Habilitacao habilitacao			) {
		if (habilitacao == null) return null;
		
		ArrayList<Integer> restrictions=new ArrayList<Integer>();	
		int index=0;
		String sql=this.getQuery();
		
			restrictions.add(0, null);

			if ((!Utils.isBlank(habilitacao.getUsuario())) &&
				(!Utils.isBlank(habilitacao.getUsuario().getId())) 				
				){
				sql+=" and idusuario = ?";
				index++;
				restrictions.add(0, index);
			}
		
		
			restrictions.add(1, null);
			if ((!Utils.isBlank(habilitacao.getServico())) &&
				(!Utils.isBlank(habilitacao.getServico().getId()))
				){
				sql+=" and idservico = ?";
				index++;
				restrictions.add(1, index);
			}
			
			restrictions.add(2, null);
			if (!Utils.isBlank(habilitacao.getId())){
				sql+=" and idhabilitacao = ?";
				index++;
				restrictions.add(2, index);
			}
		
					
		this.setQuery(sql);
		return restrictions;
	}
	
	private PreparedStatement loadModelToPreparedStatement(
			Habilitacao habilitacao, ArrayList<Integer> indexes) throws SQLException {
		PreparedStatement stmt=con.prepareStatement(this.getQuery());
		
		if (!Utils.isBlank(indexes.get(0)))
			stmt.setInt(indexes.get(0), habilitacao.getUsuario().getId());
		
		if (!Utils.isBlank(indexes.get(1)))
			stmt.setInt(indexes.get(1), habilitacao.getServico().getId());
		
		if (!Utils.isBlank(indexes.get(2)))
			stmt.setInt(indexes.get(2), habilitacao.getId());
		
		
		return stmt;
	}
	
	private Habilitacao loadResultSetToModel(ResultSet rs) throws SQLException {
		Habilitacao habilitacao = new Habilitacao();
		if (!Utils.isBlank(rs.getInt(Constantes.IDHABILITACAO)))
		habilitacao.setId(rs.getInt(Constantes.IDHABILITACAO));
		
		//busca o horario no banco de dados
		Usuario usuarioBusca = new Usuario();
		if (!Utils.isBlank(rs.getInt(Constantes.IDFUNCIONARIO)))
		usuarioBusca.setId(rs.getInt(Constantes.IDFUNCIONARIO));
		UsuarioDao usuarioDao=new UsuarioDao();
		habilitacao.setUsuario(usuarioDao.getUsuarios(usuarioBusca,0,0).get(0));
	
		//busca o horario no banco de dados
		Servico servicoBusca = new Servico();
		if (!Utils.isBlank(rs.getInt(Constantes.IDSERVICO)))
		servicoBusca.setId(rs.getInt(Constantes.IDSERVICO));
		ServicoDao servicoDao=new ServicoDao();
		habilitacao.setServico(servicoDao.getServicos(servicoBusca,0,0).get(0));
		
		return habilitacao;
	}
	
	
	public PreparedStatement apagar(Habilitacao habilitacao) throws SQLException {
		String sql = "delete from habilitacao where idhabilitacao = ?";
		
		

		this.setQuery(sql);
		ArrayList<Integer> indexes=new ArrayList<Integer>();
		for (int i=0;i<numerodecampos;i++)
			indexes.add(i,null);
		indexes.add(numerodecampos,1);
		PreparedStatement stmt = this.loadModelToPreparedStatement(habilitacao, indexes);
		
		stmt.execute();
		this.setQuery(stmt.toString());

		stmt.close();
		
		con.close();
		return stmt;
	}

	
	






}
